							 
*****************************
* Prepare daily consol data *
*****************************

 

clear 


*** load relevant data

		
		
		* London
		
						import excel "$Replicationdirectory/_Data/UK Consol (daily)/BondData_UKconsol_LONDON_daily.xlsx",  sheet("Sheet1") cellrange(A2:F3532) firstrow clear
						
 						
						tsset date, daily 
						format date %d
						
					
						destring numerator, force replace

						
						destring denominator, force replace
						
						
						replace BritishConsol=BritishConsol+numerator/denominator
						 
						rename BritishConsol Consol_London_GBP

						
						
						*** drop sat and sun
						drop if weekday_n==6 | weekday_n==7
						
						
						keep date Consol_London_GBP
						
						save "$Replicationdirectory/_Work/TEMP/CONSOL_DAILY_LONDON" , replace
		
		 
		* Paris
		
						import excel "$Replicationdirectory/_Data/UK Consol (daily)/BondData_UKconsol_PARIS_daily.xlsx",  sheet("Sheet1") cellrange(A2:F3532) firstrow clear
						
						
						destring Consol, force replace
						
						
						  tsset date, daily
						 format date %d

						
						
						
							*** get exchange rates
							
							
							merge 1:1 date using "$Replicationdirectory/_Work/TEMP/_data_bonds.dta", force
							
							keep date Consol_Paris_FF spotm_fr weekday_n
							
										tsset date, daily

							
							* convert to GBP
							gen Consol_Paris_GBP=Consol_Paris_FF/spotm_fr/100
							
							
							replace Consol_Paris_GBP=Consol_Paris_FF/(spotm_fr/(1.25/100)) if tin(03aug1931,30dec1933)
							replace Consol_Paris_GBP=Consol_Paris_FF/(spotm_fr/(25/100)) if tin(1jan1930,31jul1931)

							
						
						
						
						
 
 
			
 			
			
			* plot London and Paris  yields 	
			
			
						merge 1:1 date using "$Replicationdirectory/_Work/TEMP/CONSOL_DAILY_LONDON"
						
						drop if weekday_n==6 | weekday_n==7
						
						label variable Consol_Paris_GBP "Consol in Paris"
						
						label var Consol_London_GBP "Consol in London"

						 
						gen y_consol_Paris=100* 2.5/Consol_Paris_GBP
						gen y_consol_London=100* 2.5/Consol_London_GBP 
						
						label var y_consol_Paris "Consol in Paris"
						label var y_consol_London "Consol in London"
						
						*	twoway (line  y_consol_Paris y_consol_London date	, lcolor( blue red) ylabel(0(1)5, labsize(small)) yscale(range(0 5)) bgcolor(white)  graphregion(color(white))  bgcolor(white)  bgcolor(white)  tline(14jun1934, lp(dash) lc(black)  ) tline(1oct1936, lp(dash) lc(black)  ) tlabel(01jan1930 "1930" 01jan1931 "1931" 01jan1932 "1932" 01jan1933 "1933" 01jan1934 "1934" 01jan1935 "1935" 01jan1936 "1936" 01jan1937 "1937" 01jan1938 "1938" 01jan1939 "1939" ,angle(45) labsize(mediumsmall) ) xtitle("Year") legend(c(4)  size(small) region(lcolor(none) ))  ytitle("Current yield (in %)") ylabel(, nogrid)  ) 
			
			 
	*** Prepare data for use 
	
						* make last available value interpolation 
						tsset date, daily
						sort date
						gen time =_n 
						tsset time
						sort time
						  
						
						 foreach market in Paris London   {
												gen y_consol_`market'_lastavail=y_consol_`market'
												replace y_consol_`market'_lastavail=y_consol_`market'_lastavail[_n-1]  if y_consol_`market' >=. 
												gen dy_consol_`market'=d.y_consol_`market'_lastavail
												
												gen P_consol_`market'_lastavail=Consol_`market'_GBP
												replace P_consol_`market'_lastavail=P_consol_`market'_lastavail[_n-1]  if Consol_`market'_GBP >=. 
												
														}
								
							  

								
								rename date daten

								
								sort time
								 


					 
													*create dummy for default
																gen default=1 if date>td(14jun1934)
																replace default=0 if default==.
																
																label var default "Germany in default (from June 14, 1934) onwards"
						
					 
						drop _merge
								
						save "$Replicationdirectory/_Work/TEMP/_data_consol_daily", replace	
   
 
   * save in panel structure
						keep date   P_consol_Paris_lastavail Consol_Paris_GBP  P_consol_London_lastavail Consol_London_GBP 
				   
						rename  P_consol_Paris_lastavail		P_lastavail_consol_Paris
						rename  Consol_Paris_GBP				P_consol_Paris
						rename  P_consol_London_lastavail		P_lastavail_consol_London
						rename  Consol_London_GBP				P_consol_London
						
					
								preserve 
										keep P_lastavail_consol_Paris P_consol_Paris date 
										rename P_consol_Paris P_consol
										rename P_lastavail_consol_Paris P_lastavail_consol
										gen bond="Consol"
										gen market="paris"
										save "$Replicationdirectory/_Work/TEMP/_data_consol_daily_parisonly" , replace 
								restore 
								
								
								 
								

								
								
								keep P_lastavail_consol_London P_consol_London date 
								rename P_consol_London P_consol
								rename P_lastavail_consol_London P_lastavail_consol
								gen bond="Consol"
								gen market="london"
								 
								
								 
								
								append using  "$Replicationdirectory/_Work/TEMP/_data_consol_daily_parisonly"
								
								rename daten date
								
								save "$Replicationdirectory/_Work/TEMP/_data_consol_daily_panel", replace 
